import pymysql
import logging


# 配置日志
logging.basicConfig(
    level=logging.INFO,  # 设置日志级别为 INFO
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'  # 设置日志格式
)

class MysqlUtil:
    def __init__(self):
        '''
        初始化方法，连接数据库
        '''
        self.host = '124.71.58.131'    # 主机名
        self.user = 'root'
        self.password = 'my-secret-pw'     # 数据库密码
        self.database = 'ce' # 数据库名称
        self.PORT = 13389  #数据库端口


    def _connect(self):
        '''连接数据库并返回连接对象和游标'''
        self.db = pymysql.connect(host=self.host, user=self.user, password=self.password,  port=self.PORT,  db=self.database)
        self.cursor = self.db.cursor(cursor=pymysql.cursors.DictCursor)


    def _close(self):
        '''关闭数据库连接和游标'''
        if hasattr(self, 'cursor'):
            self.cursor.close()
        if hasattr(self, 'db'):
            self.db.close()

    def insert(self, sql, params):
        '''
        插入数据库
        sql: 插入数据库的sql语句
        '''
        try:
            self._connect()
            self.cursor.execute(sql,params)
            self.db.commit()
            return self.cursor.lastrowid
        except Exception as e:
            logging.error("插入操作失败", exc_info=True)
            self.db.rollback()
        finally:
            self._close()

    def fetchone(self, sql, params):
        '''
        查询数据库：单个结果集
        '''
        result = None
        try:
            self._connect()
            if params:
              self.cursor.execute(sql,params)
            else:
                self.cursor.execute(sql)
            result = self.cursor.fetchone()
        except Exception as e:
            logging.error("查询操作失败", exc_info=True)
            self.db.rollback()
        finally:
            self._close()
        return result

    def fetchall(self, sql):
        '''
        查询数据库：多个结果集
        '''
        results = []
        try:
            self._connect()
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
        except Exception as e:
            logging.error("查询操作失败", exc_info=True)
            self.db.rollback()
        finally:
            self._close()
        return results

    def fetchalls(self, sql, params):
        '''
        查询数据库：多个结果集
        '''
        results = []
        try:

            self._connect()
            if params:
                  self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            results = self.cursor.fetchall()
        except Exception as e:
            logging.error("查询操作失败", exc_info=True)
            self.db.rollback()
        finally:
            self._close()
        return results

    def delete(self, sql):
        '''
        删除结果集
        '''
        try:
            self._connect()
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            logging.error("删除操作失败", exc_info=True)
            self.db.rollback()
        finally:
            self._close()

    def update(self, sql):
        '''
        更新结果集
        '''
        try:
            self._connect()
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            logging.error("更新操作失败", exc_info=True)
            self.db.rollback()
        finally:
            self._close()

    def wo(self, sql):
        '''
        查询数据库：单个结果集
        '''
        result = None
        try:
            self._connect()
            self.cursor.execute(sql)
            result = self.cursor.fetchone()
        except Exception as e:
            logging.error("查询操作失败", exc_info=True)
            self.db.rollback()
        finally:
            self._close()
        return result